import pyodbc
import pandas as pd
# 创建连接字符串
conn_str = (
    r'DRIVER={SQL Server Native Client 10.0};'
    r'SERVER=BF-202403241716;'
    r'DATABASE=scott;'
    r'Trusted_Connection=Yes;'
)
# 建立连接
cnxn = pyodbc.connect(conn_str)
# 创建游标对象
cursor = cnxn.cursor()
# 执行SQL查询
query = "SELECT * FROM dbo.salgrade"
cursor.execute(query)
# 获取查询结果
data1 = cursor.fetchall()
print(type(data1))
print(data1)

# 获取列名
columns1 = [column[0] for column in cursor.description]
#print(type(columns1))
#print(columns1)
# 将元组列表展开为一维数组
#data1 = [list(item) for item in data1]
print(type(data1))
print(data1)
# 将结果转换为DataFrame
df1 = pd.DataFrame(data1, columns=columns1)
print(df1)


# 将数据写入Excel文件
df1.to_excel('output.xlsx', index=False)

# 关闭数据库连接
cursor.close()
cnxn.close()


